/**
 * @author Pedro J Toro
 * csc8205 Advance Java - Assignment 02
 * This program parses an XML file and writes is to a database.
 */

import java.io.*;

// DOM classes.
import org.w3c.dom.Document;
import org.w3c.dom.Node;

// JAXP
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

// SQL
import java.sql.*;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class ParseXML {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws ParserConfigurationException, SAXException, IOException, ClassNotFoundException {
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        dbf.setNamespaceAware(true);
        dbf.setIgnoringElementContentWhitespace(true);

        String fileName;
        if (args.length == 0) {
            fileName = "assg2.xml";
        } else {
            fileName = args[0];
        }
        
        DocumentBuilder builder = dbf.newDocumentBuilder();
        Document xmldoc = builder.parse(fileName);
        Node root = xmldoc.getDocumentElement();

        Node currentNode = null;
        Node subNode = null;
        NodeList childNodes = root.getChildNodes();

        String colNames, colValues;
        String SQL_insert = "";

        try {
            // load database driver class 
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            // connect to database 
            Connection conn = DriverManager.getConnection("jdbc:odbc:xmltest");
            // create statement to query database 
            Statement stm = conn.createStatement();


            for (int i = 0; i < childNodes.getLength(); i++) {
                currentNode = childNodes.item(i);

                //System.out.println(currentNode.getNodeName());

                colNames = "";
                colValues = "";

                for (int j = 0; j < currentNode.getChildNodes().getLength(); j++) {
                    subNode = currentNode.getChildNodes().item(j);

                    //System.out.println("    " + subNode.getNodeName() + " -> " + subNode.getTextContent());

                    // check for null values
                    if (subNode.getTextContent().length() > 0) {
                        if (j == 0) {
                            colNames += subNode.getNodeName();
                            colValues += "'" + subNode.getTextContent() + "'";
                        } else {
                            colNames += ", " + subNode.getNodeName();
                            colValues += ", '" + subNode.getTextContent() + "'";
                        }
                    }
                }

                // create SQL insert statement
                SQL_insert = "Insert into assg2T2 (" + colNames + ") Values (" + colValues + ")";
                //System.out.println(SQL_insert);

                // execute SQL insert command
                stm.executeUpdate(SQL_insert);
            }

            // close connection 
            stm.close();
            conn.close();
        } catch (SQLException exp) {
            System.out.println(exp.toString());
        }
    }
}
